﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

/// <summary>
/// Opsamling af data
/// De indsamlede data fra pumpestationerne gemmes i en database med henblik på senere at kunne lave statistik.
/// </summary>
public class StationGateway
{
    //private string connectionString = "Data Source = ealdb1.eal.local,"

    private DBHelper dbHelper;
    
    public StationGateway()
    {
        dbHelper = new DBHelper("ealdb1.eal.local", "EJL05_DB", "ejl05_usr", "Baz1nga5");
        dbHelper.Connect();
    }

    public bool AddStation(string ipAdress, int port, double waterLevel, int status)
    {
        dbHelper.Open();
        bool insert = dbHelper.InsertRow("Laks_Station",
                                         "IpAdress, Port, Status, WaterLevel",
                                         String.Format("'{0}', '{1}', '{2}', '{3}'",
                                         ipAdress, port, status, waterLevel));
        dbHelper.Close();
        return insert;
    }

    public bool AddPump(double capacity, int status, int stationId)
    {
        //string sql = "insert into Laks_Pump (StationId, Capacity, Status) values (@stationId, @capacity, @status)";
        //return ExecuteNonQuery(sql, CommandType.Text,"@stationId", stationId, "@capacity",capacity, "@status",status);
        dbHelper.Open();
        bool insert = dbHelper.InsertRow("Laks_Pump",
                                         "StationId, Capacity, Status",
                                         String.Format("'{0}', '{1}', '{2}'",
                                         stationId, capacity, status));
        dbHelper.Close();
        return insert;
    }

    public bool EditStation(int id, string ipAdress, int port, double waterLevel, int status)
    {
        //string sql = "update Laks_Station set ipAdress = " + ipAdress + ",port = " + port + ",condition = " + waterLevel + ", status =" + status + "where id = " + id;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Station",
                                         String.Format("IpAdress = '{0}', Port = '{1}', WaterLevel = '{2}', Status = '{3}'",
                                         ipAdress, port, waterLevel, status),
                                         "Id = " + id);
        dbHelper.Close();
        return update;
    }

    public bool EditPump(double capacity, int status, int pumpId)
    {
        //string sql = "update Laks_Station set capacity = " + capacity + ", status =" + status + "stationId =" + stationId;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Pump",
                                         String.Format("Capacity = {0}, Status = {1}",
                                         capacity, status),
                                         "Id = " + pumpId);
        dbHelper.Close();
        return update;
    }

    public bool RemovePump(int id)
    {
        //string sql = "delete from Laks_Pump where id =" + id;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool delete = dbHelper.DeleteRow("Laks_Pump",
                                         String.Format("Id = {0}",
                                         id));
        dbHelper.Close();
        return delete;
    }

    // Hej rud
    public bool RemovePumpsByStationId(int stationId)
    {
        dbHelper.Open();
        bool delete = dbHelper.DeleteRow("Laks_Pump",
                                         String.Format("StationId = {0}",
                                         stationId));
        dbHelper.Close();
        return delete;
    }

    public bool RemoveStation(int id)
    {
        //string sql = "delete from Laks_Pump where id =" + id;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool delete = dbHelper.DeleteRow("Laks_Station",
                                         String.Format("Id = {0}",
                                         id));
        dbHelper.Close();
        return delete;
    }

    public void RequestCurrentWaterLevel()
    {
        throw new NotImplementedException();
    }

    public bool StartStation(int id)
    {
        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Station",
                                         "Status = 1",
                                         "id = " + id);
        dbHelper.Close();
        return update;   
    }
    public bool StartPump(int id)
    {
        //string sql = "update Laks_Pump set status = 1 where id =" + id;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Pump",
                                         "Status = 1",
                                         "StationId = " + id);
        dbHelper.Close();
        return update;
    }
    public bool StopStation(int id)
    {
        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Station",
                                         "Status = 0",
                                         "id = " + id);
        dbHelper.Close();
        return update;   
    }
    public bool StopPump(int id)
    {
        //string sql = "update Laks_Pump set status = 0 where id =" + id;
        //return ExecuteNonQuery(sql, CommandType.Text);

        dbHelper.Open();
        bool update = dbHelper.UpdateRow("Laks_Pump",
                                         "Status = 0",
                                         "Id = " + id);
        dbHelper.Close();
        return update;
    }

    public List<Station> RequestInfoForStations()
    {
        string sql = "select Id, IpAdress, Port, Status, WaterLevel from Laks_Station";
        Station station;
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        List<Station> stations = new List<Station>();
        foreach (DataRow row in table.Rows)
        {
            station = new Station();
            station.Id = (int)row["id"];
            station.Port = (int)row["port"];
            station.IPAdresse = (string)row["ipAdress"];
            station.Status = (int)row["status"];
            station.WaterLevel = (double)row["waterLevel"];
            stations.Add(station);

        }
        return stations;
    }
    public List<Pump> GetPump(int stationid)
    {
        Pump pump = null;
        string sql = "select Id, StationId, Capacity, Status from Laks_Pump where stationId = " + stationid;
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        List<Pump> pumps = new List<Pump>();
        foreach (DataRow row in table.Rows)
        {
            pump = new Pump();
            pump.Id = (int)row["id"];
            pump.Capacity = (double)row["capacity"];
            pump.Status = (int)row["status"];
            pumps.Add(pump);
        }
        return pumps;
    }

    public Station FindStation(int stationId)
    {
        string sql = "SELECT Id, IpAdress, Port, Status, WaterLevel FROM Laks_Station WHERE Id = '" + stationId +
                     "' ORDER BY Id";
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        var stations = (from DataRow r in table.Rows
                        select new Station
                            {
                                Id = (int)r["Id"],
                                IPAdresse = r["IpAdress"].ToString(),
                                Port = (int)r["Port"],
                                Status = (int)r["Status"],
                                WaterLevel = (double)r["WaterLevel"]
                            }).ToList();
        return stations[0];
    }
    public Pump FindPump(int pumpId)
    {
        string sql = "SELECT Id, StationId, Capacity, Status FROM Laks_Pump WHERE Id '" + pumpId + "' ORDER BY Id";
        DataTable table = ExcuteQuery(sql, CommandType.Text);
        var pumps = (from DataRow r in table.Rows
                     select new Pump
                         {
                             Id = (int)r["Id"],
                             Capacity = (double)r["Capacity"],
                             Status = (int)r["Status"]
                         }).ToList();
        return pumps[0];
    }

    public bool ExecuteNonQuery(string sql, CommandType commandType, params object[] pars)
    {
        bool result = false;
        dbHelper.Open();
        result = dbHelper.ExcuteNonQuery(sql, commandType, pars);
        dbHelper.Close();
        return result;
    }
    public DataTable ExcuteQuery(string sql, CommandType commandType, params object[] pars)
    {
        DataTable table = null;
        dbHelper.Open();
        table = dbHelper.ExcuteQuery(sql, commandType, pars);
        dbHelper.Close();
        return table;
    }
}